package com.pb.infra.oth.util;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * EXCEL帮助类
 *
 * @Author:jinyang
 * @Date: 2019/8/7
 * @Time: 00:00
 */
public class ExcelUtil {

    private static NumberFormat numberFormat = NumberFormat.getNumberInstance();

    static {
        numberFormat.setGroupingUsed(false);
    }

    private ExcelUtil() {
        throw new IllegalStateException("Utility class");
    }

    /**
     * 获取单元格对象
     *
     * @param sheetAt 表单
     * @param rowNo   行号
     * @param cellNo  列号
     * @return XSSFCell 单元格对象
     */
    public static XSSFCell getCell(XSSFSheet sheetAt, int rowNo, int cellNo) {
        XSSFRow row = sheetAt.getRow(rowNo);
        if (row == null) {
            row = sheetAt.createRow(rowNo);
            return row.createCell(cellNo);
        } else {
            XSSFCell cell = row.getCell(cellNo);
            if (cell == null) {
                return row.createCell(cellNo);
            } else {
                return cell;
            }
        }
    }

    /**
     * 读取字符类型的单元格数据
     *
     * @param cell 单元格
     * @return String 数值
     */
    public static String getStringCellValue(Cell cell) {
        if (Objects.isNull(cell)) {
            return "";
        }
        //DecimalFormat df = new DecimalFormat("#.00");
        String value = "";
        String res = "^[+-]?[\\d]+([\\.][\\d]+)?([Ee][+-]?[\\d]+)?$";
        switch (cell.getCellType()) {
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case STRING:
                value = cell.getRichStringCellValue().toString();
                break;
            case FORMULA:
                break;
            case NUMERIC:
                if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
                        || "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
                        || "yy/m/d".equals(cell.getCellStyle().getDataFormatString())
                        || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
                        || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())) {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 用于转化为日期格式
                        Date d = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        value = formater.format(d);
                    }
                } else {
                    value = String.valueOf(numberFormat.format(cell.getNumericCellValue()));
                    if (value.indexOf('.') > -1 && !value.matches(res)) {
                        String val = value.substring(value.indexOf('.') + 1, value.length());
                        if ("0".equals(val)) {
                            value = value.substring(0, value.indexOf('.'));
                        }
                    }
                }
                break;
            case BLANK:
                break;
            case ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            case _NONE:
                break;
        }
        if (value.matches(res)) {
            //保留小数点后两位四舍五入
            //BigDecimal val = new BigDecimal(ExcelUtil.getDoubleNumber(value));
            value = new BigDecimal(ExcelUtil.getDoubleNumber(value)).toPlainString();
            /*if (value.indexOf(".") > -1) {
                value = df.format(val);
            }*/
        }
        return value;
    }

    public static String getStringCellValue(Cell cell, Workbook workbook) {
        if (Objects.isNull(cell)) {
            return "";
        }
        //DecimalFormat df = new DecimalFormat("#.00");
        String value = "";
        String res = "^[+-]?[\\d]+([\\.][\\d]+)?([Ee][+-]?[\\d]+)?$";
        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        switch (cell.getCellType()) {
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case STRING:
                value = cell.getRichStringCellValue().toString();
                break;
            case FORMULA:
                switch (evaluator.evaluate(cell).getCellType()) {
                    case BOOLEAN:
                        value = String.valueOf(evaluator.evaluate(cell).getBooleanValue());
                        break;
                    case STRING:
                        value = evaluator.evaluate(cell).getStringValue();
                        break;
                    case ERROR:
                        value = String.valueOf(evaluator.evaluate(cell).getErrorValue());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Date date = DateUtil.getJavaDate(evaluator.evaluate(cell).getNumberValue());
                            value = formater.format(date);
                        } else {
                            value = String.valueOf(numberFormat.format(evaluator.evaluate(cell).getNumberValue()));
                            if (value.indexOf('.') > -1 && !value.matches(res)) {
                                String val = value.substring(value.indexOf('.') + 1, value.length());
                                if ("0".equals(val)) {
                                    value = value.substring(0, value.indexOf('.'));
                                }
                            }
                        }
                }
                break;
            case NUMERIC:
                if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
                        || "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
                        || "yy/m/d".equals(cell.getCellStyle().getDataFormatString())
                        || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
                        || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())) {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 用于转化为日期格式
                        Date d = cell.getDateCellValue();
                        value = formater.format(d);
                    }
                } else {
                    value = String.valueOf(numberFormat.format(cell.getNumericCellValue()));
                    if (value.indexOf('.') > -1 && !value.matches(res)) {
                        String val = value.substring(value.indexOf('.') + 1, value.length());
                        if ("0".equals(val)) {
                            value = value.substring(0, value.indexOf('.'));
                        }
                    }
                }
                break;
            case BLANK:
                break;
            case ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            case _NONE:
                break;
        }
        if (!StringUtil.isEmpty(value) && value.matches(res)) {
            //保留小数点后两位四舍五入
            //BigDecimal val = new BigDecimal(ExcelUtil.getDoubleNumber(value)); ExcelUtil.getDoubleNumber()
            value = new BigDecimal(value).toPlainString();
            /*if (value.indexOf(".") > -1) {
                value = df.format(val);
            }*/
        }
        return value;
    }

    /**
     * 浏览器下载excel
     *
     * @param filename 下载文件名
     * @param workbook 工作薄
     * @param response 请求返回对象
     * @throws Exception
     */
    public static void buildExcelDocument(String filename, XSSFWorkbook workbook, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 创建头部提示文字框
     *
     * @param workbook 工作簿
     * @param sheet    表单
     * @param info     提示文字内容
     * @param lastRow  合并单元格最终行号
     * @param lastCol  合并单元格最终列号
     */
    public static void createHeadInfo(XSSFWorkbook workbook, XSSFSheet sheet, String info, int lastRow, int lastCol) {
        //设置为居中加粗
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");//字体
        font.setColor(IndexedColors.RED.getIndex());//字体颜色
        style.setVerticalAlignment(VerticalAlignment.TOP);//垂直靠上
        style.setFont(font);
        style.setWrapText(true);
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(new XSSFRichTextString(info));
        cell.setCellStyle(style);
        CellRangeAddress cra = new CellRangeAddress(0, lastRow, 0, lastCol);
        sheet.addMergedRegion(cra);
        // 合并后利用RegionUtil加边框
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
    }

    /**
     * 创建头部提示文字框
     *
     * @param workbook 工作簿
     * @param sheet    表单
     * @param info     提示文字内容
     * @param lastRow  合并单元格最终行号
     * @param lastCol  合并单元格最终列号
     */
    public static void createHeadInfo1(XSSFWorkbook workbook, XSSFSheet sheet, String info, int lastRow, int lastCol, String info1) {
        //设置为居中加粗
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFCellStyle style1 = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");//字体
        font.setColor(IndexedColors.BLACK.getIndex());//字体颜色
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFont(font);
        style.setWrapText(true);
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(new XSSFRichTextString(info));
        cell.setCellStyle(style);
        style1.setAlignment(HorizontalAlignment.LEFT);//水平居中
        XSSFRow row1 = sheet.createRow(lastRow + 1);
        XSSFCell cell1 = row1.createCell(0);
        cell1.setCellType(CellType.STRING);
        cell1.setCellValue(new XSSFRichTextString(info1));
        cell1.setCellStyle(style);
        cell1.setCellStyle(style1);
        CellRangeAddress cra = new CellRangeAddress(0, lastRow, 0, lastCol);
        sheet.addMergedRegion(cra);
        CellRangeAddress cra1 = new CellRangeAddress(lastRow + 1, lastRow + 1, 0, lastCol);
        sheet.addMergedRegion(cra1);
        // 合并后利用RegionUtil加边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra1, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra1, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cra1, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        for (int i = 0; i < colWidths.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createMergeReportTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        int t = 1;
        for (int i = 0; i < colWidths.length; i++) {
            if (i > 0 && i < 3) {
                CellRangeAddress cra = new CellRangeAddress(4, 4, t, t + 4);
                sheet.addMergedRegion(cra);
                t = t + 5;
            } else if (i < 1) {
                CellRangeAddress cra = new CellRangeAddress(4, 5, i, i);
                sheet.addMergedRegion(cra);
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        int j = 0;
        int k = 1;
        int e = 1;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 11; r++) {
            cellb = row.createCell(r);
            cellb.setCellStyle(style);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i < 3) {
                if (i > 0) {
                    cell = row.createCell(e);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(style);
                    e = e + 5;
                } else {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            } else {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 1;
            }
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createMergemReportTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas, String[] titleasm) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        XSSFRow rowds = sheet.createRow(headerRowIndex + 2);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        int t = 1;
        int l = 1;
        for (int i = 0; i < colWidths.length; i++) {
            if (i > 0 && i < 3) {
                CellRangeAddress cra = new CellRangeAddress(4, 4, t, t + 9);
                sheet.addMergedRegion(cra);
                t = t + 10;
            } else if (i < 1) {
                CellRangeAddress cra = new CellRangeAddress(4, 6, i, i);
                sheet.addMergedRegion(cra);
            } else {
                CellRangeAddress cra = new CellRangeAddress(5, 5, l, l + 1);
                sheet.addMergedRegion(cra);
                l = l + 2;
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        XSSFCell cellc;
        int j = 0;
        int k = 1;
        int e = 1;
        int d = 1;
        int h = 0;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 10; r++) {
            cellb = rowd.createCell(r);
            cellb.setCellStyle(style1);
        }
        for (int r = 0; r < 21; r++) {
            cellc = row.createCell(r);
            cellc.setCellStyle(style);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i < 3) {
                if (i > 0) {
                    cell = row.createCell(e);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(style);
                    e = e + 10;
                } else {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            } else if (i > 12) {
                cella = rowds.createCell(d);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleasm[h]);
                cella.setCellStyle(style);
                h = h + 1;
                d = d + 1;
            } else {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 2;
            }
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createMergetTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        int h = titles.length - 1;
        int t = 1;
        for (int i = 0; i < colWidths.length; i++) {
            if (i > 0 && i < h + 1) {
                CellRangeAddress cra = new CellRangeAddress(4, 4, t, t + 1);
                sheet.addMergedRegion(cra);
                t = t + 2;
            } else if (i < 1) {
                CellRangeAddress cra = new CellRangeAddress(4, 5, i, i);
                sheet.addMergedRegion(cra);
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        int j = 0;
        int k = 1;
        int e = 1;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 5; r++) {
            cellb = rowd.createCell(r);
            cellb.setCellStyle(style1);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i > h) {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 1;
            } else {
                if (i > 0) {
                    cell = row.createCell(e);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(style);
                    e = e + 2;
                } else {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            }
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createMergeTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        int t = 5;
        for (int i = 0; i < colWidths.length; i++) {
            if (i > 4 && i < 10) {
                CellRangeAddress cra = new CellRangeAddress(5, 5, t, t + 1);
                sheet.addMergedRegion(cra);
                t = t + 2;
            } else if (i < 5) {
                CellRangeAddress cra = new CellRangeAddress(5, 6, i, i);
                sheet.addMergedRegion(cra);
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        int j = 0;
        int k = 5;
        int e = 7;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 5; r++) {
            cellb = rowd.createCell(r);
            cellb.setCellStyle(style1);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i < 10) {
                if (i > 5) {
                    cell = row.createCell(e);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(style);
                    e = e + 2;
                } else {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            }
            if (i > 4) {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 1;
            }
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createMergeProductTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        int t = 5;
        for (int i = 0; i < colWidths.length; i++) {
            if (i > 4 && i < 10) {
                CellRangeAddress cra = new CellRangeAddress(5, 5, t, t + 9);
                sheet.addMergedRegion(cra);
                t = t + 10;
            } else if (i < 5) {
                CellRangeAddress cra = new CellRangeAddress(5, 6, i, i);
                sheet.addMergedRegion(cra);
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        int j = 0;
        int k = 5;
        int e = 15;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 5; r++) {
            cellb = rowd.createCell(r);
            cellb.setCellStyle(style1);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i < 10) {
                if (i > 5) {
                    cell = row.createCell(e);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(style);
                    e = e + 10;
                } else {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            }
            if (i > 4) {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 1;
            }
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createDebtAdjustmentTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas, int zhi) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i == 4) {
                CellRangeAddress cra = new CellRangeAddress(5, 5, 4, zhi);
                sheet.addMergedRegion(cra);
            } else if (i < 4) {
                CellRangeAddress cra = new CellRangeAddress(5, 6, i, i);
                sheet.addMergedRegion(cra);
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        int j = 0;
        int k = 4;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 4; r++) {
            cellb = rowd.createCell(r);
            cellb.setCellStyle(style1);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i < 5) {
                cell = row.createCell(i);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(titles[i]);
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                cell.setCellStyle(style);
            }
            if (i > 3) {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 1;
            }
        }
    }

    /**
     * 创建表头
     *
     * @param workbook       工作簿
     * @param sheet          表单
     * @param titles         列头
     * @param colWidths      列宽
     * @param headerRowIndex 表格头所在行号
     */
    public static void createMergeSenProductTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, int[] colWidths, int headerRowIndex, String[] titleas) {
        XSSFRow row = sheet.createRow(headerRowIndex);
        XSSFRow rowd = sheet.createRow(headerRowIndex + 1);
        for (int i = 0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, colWidths[i]);
        }
        int t = 5;
        for (int i = 0; i < colWidths.length; i++) {
            if (i > 4 && i < 7) {
                CellRangeAddress cra = new CellRangeAddress(5, 5, t, t + 9);
                sheet.addMergedRegion(cra);
                t = t + 10;
            } else if (i < 5) {
                CellRangeAddress cra = new CellRangeAddress(5, 6, i, i);
                sheet.addMergedRegion(cra);
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);//加粗
        fontTitle.setFontName("微软雅黑");//字体
        fontTitle.setColor(IndexedColors.WHITE.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景色和前景色的时候要加上，否则设置颜色不起作用
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(fontTitle);
        XSSFCell cell;
        XSSFCell cella;
        XSSFCell cellb;
        int j = 0;
        int k = 5;
        int e = 15;
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderRight(BorderStyle.THIN);//右边框
        for (int r = 0; r < 5; r++) {
            cellb = rowd.createCell(r);
            cellb.setCellStyle(style1);
        }
        for (int i = 0; i < colWidths.length; i++) {
            if (i < 7) {
                if (i > 5) {
                    cell = row.createCell(e);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(style);
                    e = e + 10;
                } else {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(titles[i]);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            }
            if (i > 4) {
                cella = rowd.createCell(k);
                cella.setCellType(CellType.STRING);
                cella.setCellValue(titleas[j]);
                cella.setCellStyle(style);
                j = j + 1;
                k = k + 1;
            }
        }
    }

    /**
     * 创建数据部分
     *
     * @param workbook    工作簿
     * @param dataList    数据集
     * @param sheet       表单
     * @param rowBeginNum 表体起始行号
     * @param colNames    每行数据在Map对象中的Key集
     * @param types       每列数据类型（STRING、NUMBER、NUMBER2、NUMBER4、NUMBER6）
     */
    public static void createBody(XSSFWorkbook workbook, List<Map<String, Object>> dataList, XSSFSheet sheet, int rowBeginNum, String[] colNames, String[] types) {
        //普通样式
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");//字体
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(font);
        style.setLocked(true);//设置cell所引用的样式是否锁住
        style.setWrapText(true);//设置自动换行
        //NUMBER样式（整数）
        XSSFCellStyle numberStyle = workbook.createCellStyle();
        numberStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        numberStyle.setBorderBottom(BorderStyle.THIN); //下边框
        numberStyle.setBorderLeft(BorderStyle.THIN);//左边框
        numberStyle.setBorderTop(BorderStyle.THIN);//上边框
        numberStyle.setBorderRight(BorderStyle.THIN);//右边框
        numberStyle.setFont(font);
        numberStyle.setLocked(true);//设置cell所引用的样式是否锁住
        numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));//无小数
        //NUMBER样式（2位小数）
        XSSFCellStyle number2Style = workbook.createCellStyle();
        number2Style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        number2Style.setBorderBottom(BorderStyle.THIN); //下边框
        number2Style.setBorderLeft(BorderStyle.THIN);//左边框
        number2Style.setBorderTop(BorderStyle.THIN);//上边框
        number2Style.setBorderRight(BorderStyle.THIN);//右边框
        number2Style.setFont(font);
        number2Style.setLocked(true);//设置cell所引用的样式是否锁住
        number2Style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));//2位小数
        //NUMBER样式（4位小数）
        XSSFCellStyle number4Style = workbook.createCellStyle();
        number4Style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        number4Style.setBorderBottom(BorderStyle.THIN); //下边框
        number4Style.setBorderLeft(BorderStyle.THIN);//左边框
        number4Style.setBorderTop(BorderStyle.THIN);//上边框
        number4Style.setBorderRight(BorderStyle.THIN);//右边框
        number4Style.setFont(font);
        number4Style.setLocked(true);//设置cell所引用的样式是否锁住
        number4Style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.0000"));//4位小数
        //NUMBER样式（6位小数）
        XSSFCellStyle number6Style = workbook.createCellStyle();
        number6Style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        number6Style.setBorderBottom(BorderStyle.THIN); //下边框
        number6Style.setBorderLeft(BorderStyle.THIN);//左边框
        number6Style.setBorderTop(BorderStyle.THIN);//上边框
        number6Style.setBorderRight(BorderStyle.THIN);//右边框
        number6Style.setFont(font);
        number6Style.setLocked(true);//设置cell所引用的样式是否锁住
        number6Style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.000000"));//6位小数
        for (Map<String, Object> data : dataList) {
            XSSFRow row = sheet.createRow(rowBeginNum);
            for (int i = 0; i < colNames.length; i++) {
                XSSFCell cell = row.createCell(i);
                if ("STRING".equals(types[i])) {
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(StringUtil.isNull(data.get(colNames[i])));
                } else if (types[i].startsWith("NUMBER")) {
                    cell.setCellType(CellType.NUMERIC);
                    if ("NUMBER".equals(types[i])) {
                        cell.setCellStyle(numberStyle);
                    } else if ("NUMBER2".equals(types[i])) {
                        cell.setCellStyle(number2Style);
                    } else if ("NUMBER4".equals(types[i])) {
                        cell.setCellStyle(number4Style);
                    } else if ("NUMBER6".equals(types[i])) {
                        cell.setCellStyle(number6Style);
                    }
                    if (!Objects.isNull(data.get(colNames[i])) && !data.get(colNames[i]).toString().trim().isEmpty()) {
                        cell.setCellValue(Double.parseDouble(data.get(colNames[i]).toString()));
                    }
                }
            }
            rowBeginNum++;
        }
    }

    public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        //设置列宽
        for (int i = 0; i <= fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum(); i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }

    public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {//合并单元格
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }

    public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
        toStyle.cloneStyleFrom(fromStyle);
    }

    public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        copyCellStyle(fromCell.getCellStyle(), newstyle);
        //样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellTypeEnum();
        // toCell.setCellType(fromCellType);
        if (fromCellType == CellType.NUMERIC) {
            toCell.setCellValue(fromCell.getNumericCellValue());
        } else if (fromCellType == CellType.STRING) {
            if (isNumber(String.valueOf(fromCell.getRichStringCellValue()))) {
                toCell.setCellValue(Double.valueOf(fromCell.getRichStringCellValue().toString()));
            } else {
                toCell.setCellValue(fromCell.getRichStringCellValue());
            }
        } else if (fromCellType == CellType.BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == CellType.ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == CellType.FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        }
    }

    public static boolean isNumber(String string) {
        Pattern pattern = Pattern.compile("^-?\\d+(\\.\\d+)?$");
        return pattern.matcher(string).matches();
    }

    public static int getExcelColNum(String col) {
        char[] colArr = col.toUpperCase(Locale.ENGLISH).toCharArray();
        int num = 0;
        for (int i = 0; i < colArr.length; i++) {
            num += (((int) colArr[i]) - 65 + 1) * Math.pow(26, colArr.length - i - 1d);
        }
        return num - 1;
    }

    public static String getExcelColIndex(int num) {
        String colIndex = "";
        double d = Math.floor(Math.log(25.0 * (num) / 26.0 + 1) / Math.log(26)) + 1;
        if (d > 1) {
            double sub = num - 26 * (Math.pow(26, d - 1) - 1) / 25;
            for (double i = d; i > 0; i--) {
                colIndex += (char) (sub / Math.pow(26, i - 1) + 65);
                sub = sub % Math.pow(26, i - 1);
            }
        } else {
            colIndex += (char) (num + 65);
        }
        return colIndex;
    }

    public static Double getDoubleNumber(String str) {
        return Double.parseDouble(new BigDecimal(str).toPlainString());
    }

    /**
     * 拼接行字符串
     *
     * @param row       行数据 XSSFRow
     * @param colTotal  列总数
     * @return
     */
    public static boolean concatString(Row row, int colTotal, DataFormatter formatter){
        StringBuffer concatStr = new StringBuffer();
        for (int i = 0; i < colTotal; i++) {
            concatStr.append(ExcelUtil.getStringCellValues(formatter, row.getCell(i)));
        }
        return StringUtil.isEmpty(concatStr.toString());
    }


    public static String getStringCellValues(DataFormatter formatter, Cell cell) {
        if (Objects.isNull(cell)) {
            return "";
        }
        return formatter.formatCellValue(cell);
    }

    public static String getCellValue(Cell cell){
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                //short s = cell.getCellStyle().getDataFormat();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        throw new RuntimeException("日期格式错误!!!");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = null;
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
